﻿using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Logging;
using NoSqlTest.Dto;
using NoSqlTest.Model;
using NoSqlTest.SugerService;
using NoSqlTest.ViewModel;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

// For more information on enabling Web API for empty projects, visit https://go.microsoft.com/fwlink/?LinkID=397860

namespace NoSqlTest.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class SqlSugerController : ControllerBase
    {
        private readonly StudentService _studentService;
        private readonly ClassGradeService _classGradeService;
        private readonly CourseService _courseService;
        private readonly MiddleStudentCourseService _middleStudentCourse;
        private readonly MiddleClassCourseCervice _middleClassCourse;
        private readonly ILogger<SqlSugerController> _logger;
        public SqlSugerController(ILogger<SqlSugerController> logger,
            StudentService studentService,
            CourseService courseService,
            MiddleStudentCourseService middleStudentCourse,
            MiddleClassCourseCervice middleClassCourse,
            ClassGradeService classGradeService)
        {
            this._studentService = studentService;
            this._courseService = courseService;
            this._classGradeService = classGradeService;
            this._middleStudentCourse = middleStudentCourse;
            this._middleClassCourse = middleClassCourse;
            this._logger = logger;
        }

        /// <summary>
        /// 添加班级
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        [HttpPost("AddClass/{name}")]
        public IActionResult AddClass(string name)
        {
            ClassGrade classGrade = new ClassGrade { Name = name };
            var classid = _classGradeService.Insert(classGrade);
            return Ok(classid);
        }
        /// <summary>
        /// 添加课程和老师
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        [HttpPost("AddCourse/{name}")]
        public IActionResult AddCourse(string name)
        {
            Course classGrade = new Course { Name = name, Teacher = name + "老师" };
            return Ok(_courseService.Insert(classGrade));
        }
        /// <summary>
        /// 给班级添加课程
        /// </summary>
        /// <param name="classAddCourse"></param>
        /// <returns></returns>
        [HttpPost("ClassAddCourse")]
        public IActionResult ClassAddCourse([FromBody] ClassAddCourse classAddCourse)
        {

            var eds = _middleClassCourse.AsQueryable().Where(t => t.ClassId == classAddCourse.ClassId).ToList();
            var Classs = new List<MiddleClassCourse>();
            foreach (var i in classAddCourse.CourseIds)
            {
                if (!eds.Select(t => t.CourseId).Contains(i))
                {
                    Classs.Add(new MiddleClassCourse
                    {
                        ClassId = classAddCourse.ClassId,
                        CourseId = i
                    });
                }
            }
            return Ok(_middleClassCourse.InsertRange(Classs));
        }
        /// <summary>
        /// 插入学生
        /// </summary>
        /// <param name="student"></param>
        /// <returns></returns>
        [HttpPost("AddStudent")]
        public IActionResult AddStudent([FromBody] AddStudent student)
        {
            try
            {
                StringBuilder str = new StringBuilder();
                Stopwatch stopwatch3 = new Stopwatch();
                stopwatch3.Start();
                List<Student> Students = new List<Student>();
                for (int i = 0; i < student.Count; i++)
                {
                    Students.Add(
                        new Student
                        {
                            ClassId = student.ClassId,
                            Name = student.Count.ToString(),
                            Age = 20,
                            Sex = 1,
                        });
                }
                _studentService.InsertRange(Students);
                stopwatch3.Stop();
                str.Append($"批量插入{student.Count}条数据耗时：" + stopwatch3.ElapsedMilliseconds.ToString());

                _logger.LogInformation(str.ToString());
                return Ok(str.ToString());
            }
            catch (Exception ex)
            {
                return Ok(ex.Message);
            }
        }
        /// <summary>
        /// 插入学生-带选修课程
        /// </summary>
        /// <param name="course"></param>
        /// <returns></returns>
        [HttpPost("AddStudentCourse")]
        public IActionResult AddStudentCourse([FromBody] AddStudentCourse course)
        {
            try
            {
                StringBuilder str = new StringBuilder();
                Stopwatch stopwatch2 = new Stopwatch();
                stopwatch2.Start();
                var service2 = _studentService.AsSugarClient();
                var service3 = _middleStudentCourse.AsSugarClient();
                for (int i = 0; i < course.StudentCount; i++)
                {
                    var id = service2.Insertable(new Student()
                    {
                        Age = 18,
                        ClassId = course.ClassId,
                        Name = i.ToString(),
                        Sex = 1,
                        Id = 0,//自增列

                    }).ExecuteReturnIdentity();
                    var Courses = new List<MiddleStudentCourse>() { new MiddleStudentCourse
                              {
                                   CourseId = course.CourseId,
                                   StudentId =id
                              }
                     };
                    service3.Insertable(Courses).ExecuteCommand();
                }
                stopwatch2.Stop();
                str.Append($"方法1 单条插入{course.StudentCount * 2}条数据耗时：" + stopwatch2.ElapsedMilliseconds.ToString());

                //Stopwatch stopwatch1 = new Stopwatch();
                //stopwatch1.Start();
                //var service = _studentService.AsSugarClient();
                //for (int i = 0; i < course.StudentCount; i++)
                //{
                //    service.Insertable(new Student()
                //    {
                //        Age = 18,
                //        ClassId = course.ClassId,
                //        Name = i.ToString(),
                //        Sex = 1,
                //        Id = 0,//自增列
                //        Courses = new List<MiddleStudentCourse>() { new MiddleStudentCourse
                //              {
                //                   CourseId = course.CourseId,
                //                   StudentId =0//需要自动获取订单的自增列
                //              }
                //        }
                //    })
                //    .AddSubList(it => it.Courses.First().StudentId)//设置item表的OrderId等于订单自增列
                //    .ExecuteReturnPrimaryKey();
                //}
                //stopwatch1.Stop();
                //str.AppendLine($"方法2 单条插入{course.StudentCount * 2}条数据耗时：" + stopwatch1.ElapsedMilliseconds.ToString());

                _logger.LogInformation(str.ToString());
                return Ok(str.ToString());
            }
            catch (Exception ex)
            {
                return Ok(ex.Message);
            }
        }
        /// <summary>
        /// 查询学生
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        [HttpGet("GetStudent/{id}")]
        public IActionResult GetStudent(int id)
        {
            try
            {
                StringBuilder str = new StringBuilder();
                Stopwatch stopwatch2 = new Stopwatch();
                stopwatch2.Start();
                var student1 = _studentService.AsQueryable()
                    .Mapper((it, cache) =>
                    {
                        var allCourses = cache.Get(ol =>
                        {
                            var olIds = ol.Select(t => t.Id).ToList();
                            return _middleStudentCourse.AsQueryable()
                                .In(a => a.StudentId, olIds)
                                .Mapper(b => b.Course, b => b.CourseId).ToList();
                        });
                        it.Courses = allCourses.Where(i => i.StudentId == it.Id).ToList();
                    })
                    .Mapper(a => a.Class, a => a.ClassId)
                    .Where(a => a.Id == id)
                    .Select(t => new StudentDto())
                    .First();
                stopwatch2.Stop();
                str.Append($"方法2查询数据耗时：" + stopwatch2.ElapsedMilliseconds.ToString());

                {
                    //Stopwatch stopwatch1 = new Stopwatch();
                    //stopwatch1.Start();
                    //var student = _studentService.AsQueryable()
                    //    //.Mapper((it, cache) =>
                    //    //{
                    //    //    var allCourses = cache.GetListByPrimaryKeys<MiddleStudentCourse>(vmodel => vmodel.Id);
                    //    //    it.Courses = allCourses.Where(i => i.StudentId == it.Id).ToList();//一对多加载失败
                    //    //})
                    //    .Mapper(a => a.Class, a => a.ClassId)
                    //    .Where(a => a.Id == id)
                    //    //.Select(t => new StudentDto())
                    //    //.Select(t => new StudentDto
                    //    //{
                    //    //    Age = t.Age,
                    //    //    //ClassName = t.Class.Name//无法实现
                    //    //})
                    //    .First();
                    //stopwatch1.Stop();
                    //str.AppendLine($"方法1查询数据耗时：" + stopwatch1.ElapsedMilliseconds.ToString());
                }

                _logger.LogInformation(str.ToString());
                return Ok(student1);
            }
            catch (Exception ex)
            {
                return Ok(ex.Message);
            }
        }

        /// <summary>
        /// 查询班级中的所有学生
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        [HttpGet("GetClassStudent/{id}")]
        public IActionResult GetClassStudent(int id)
        {
            try
            {
                StringBuilder str = new StringBuilder();
                Stopwatch stopwatch1 = new Stopwatch();
                stopwatch1.Start();

                var students = _classGradeService.AsQueryable()
                    .Mapper((it, cache) =>
                    {
                        List<Student> allStudents = cache.Get(ol =>
                        {
                            var allStudentIds = ol.Select(x => x.Id).ToList();
                            return _studentService.AsQueryable()
                                .In(a => a.ClassId, allStudentIds)
                                .ToList();
                        });
                        it.Students = allStudents.Where(a => a.ClassId == it.Id).ToList();
                    })
                    .Where(a => a.Id == id)
                    .First();
                stopwatch1.Stop();
                str.Append($"查询数据耗时：" + stopwatch1.ElapsedMilliseconds.ToString());

                _logger.LogInformation(str.ToString());
                return Ok(str.ToString());
            }
            catch (Exception ex)
            {
                return Ok(ex.Message);
            }
        }
        /// <summary>
        /// 查询学生的所有课程
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        [HttpGet("GetStudentCourse/{id}")]
        public IActionResult GetStudentCourse(int id)
        {
            try
            {
                StringBuilder str = new StringBuilder();
                Stopwatch stopwatch1 = new Stopwatch();
                stopwatch1.Start();

                var student = _studentService.AsQueryable().Where(t => t.Id == id).First();
                var db = _studentService.AsSugarClient();
                var item1 = db.Queryable<MiddleClassCourse, Course>((a, b) => new JoinQueryInfos(
                      JoinType.Left, a.CourseId == b.Id
                  ))
                 .Where(a => a.ClassId == student.ClassId)
                 .Select((a, b) => new
                 {
                     b.Id,
                     b.Name
                 }).ToList();
                var item2 = db.Queryable<MiddleStudentCourse, Course>((a, b) => new JoinQueryInfos(
                    JoinType.Left, a.CourseId == b.Id
                ))
                .Where(a => a.StudentId == id)
                .Select((a, b) => new
                {
                    b.Id,
                    b.Name
                }).ToList();
                item1.AddRange(item2);

                stopwatch1.Stop();
                str.Append($"查询数据耗时：" + stopwatch1.ElapsedMilliseconds.ToString());

                _logger.LogInformation(str.ToString());
                return Ok(str.ToString());
            }
            catch (Exception ex)
            {
                return Ok(ex.Message);
            }
        }
        /// <summary>
        /// 修改学生名
        /// </summary>
        /// <param name="student"></param>
        /// <returns></returns>
        [HttpPost("UpdateStudent")]
        public IActionResult UpdateStudent([FromBody] UpdateStudent student)
        {
            try
            {
                StringBuilder str = new StringBuilder();
                Stopwatch stopwatch1 = new Stopwatch();
                stopwatch1.Start();
                _studentService.Update(t => new Student
                {
                    Name = student.Name,
                    Age = student.Age,
                    Sex = student.Sex
                }, t => t.Id == student.Id);
                stopwatch1.Stop();
                str.Append($"方法1 修改数据耗时：" + stopwatch1.ElapsedMilliseconds.ToString());

                {
                    //Stopwatch stopwatch2 = new Stopwatch();
                    //stopwatch2.Start();
                    //_studentService.AsSugarClient().Updateable<Student>()
                    //    .SetColumns(t => t.Name == student.Name)
                    //    .SetColumns(t => t.Age == student.Age)
                    //    .SetColumns(t => t.Sex == student.Sex)
                    //    .Where(a => a.Id == student.Id)
                    //    .ExecuteCommand();
                    //stopwatch2.Stop();
                    //str.AppendLine($"方法2 修改数据耗时：" + stopwatch2.ElapsedMilliseconds.ToString());
                }

                _logger.LogInformation(str.ToString());
                return Ok(str.ToString());
            }
            catch (Exception ex)
            {
                return Ok(ex.Message);
            }
        }
    }
}
